package com.qc.dao.impl;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.qc.common.StringUtil;
import com.qc.dao.MerchantDao;
import com.qc.entity.MerchantEntity;
import com.qc.entity.search.MerchantSearchEntity;

@SuppressWarnings({ "unchecked", "rawtypes" })
@Repository("merchantDao")
public class MerchantDaoImpl implements MerchantDao{

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public int register(MerchantEntity merchantEntity) {
        String sql = "insert into t_merchant(user_name,user_pwd) values(?,?)";
        return jdbcTemplate.update(sql,new Object[]{merchantEntity.getUserName(),merchantEntity.getUserPwd()});
    }

    @Override
    public MerchantEntity loginMerchant(String userName, String password) {
        String sql = "select * from t_merchant where 1 = 1 and user_name = '"+userName+"' and user_pwd = '"+password+"' ";
        MerchantEntity merchantEntity = new MerchantEntity();
        try {
            merchantEntity = (MerchantEntity) jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(MerchantEntity.class));
        } catch (DataAccessException e) {
        }
        return merchantEntity;
    }

    @Override
    public int saveMerchant(MerchantEntity merchantEntity) {
        String sql = "update t_merchant set shop_type = ?,"
        		+ " user_name = ?,"
        		+ " user_pwd = ?,"
                + " shop_type_two = ?,"
                + " shop_type_three = ?,"
                + " shop_name = ?,"
                + " legal_person_name = ?,"
                + " province = ?,"
                + " city = ?,"
                + " region = ?,"
                + " address_detail = ?,"
                + " telphone = ?,"
                + " avg_price = ?,"
                + " intro = ?,"
                + " boss_name = ?,"
                + " mobile = ?,"
                + " mer_logo = ?,"
                + " card_front_photo = ?,"
                + " card_back_photo = ?,"
                + " business_license_photo = ?,"
                + " shop_latitude = ?,"
                + " shop_longitude = ?,"
                + " is_auth = ?,"
                + " per_capita_price = ?"
                + " where shop_id = ? ";
        return jdbcTemplate.update(sql,new Object[]{
                merchantEntity.getShopType(),merchantEntity.getUserName(),
                merchantEntity.getUserPwd(),merchantEntity.getShopTypeTwo(),
                merchantEntity.getShopTypeThree(),merchantEntity.getShopName(),
                merchantEntity.getLegalPersonName(),merchantEntity.getProvince(),
                merchantEntity.getCity(),merchantEntity.getRegion(),
                merchantEntity.getAddressDetail(),merchantEntity.getTelphone(),
                merchantEntity.getAvgPrice(),merchantEntity.getIntro(),
                merchantEntity.getBossName(),merchantEntity.getMobile(),
                merchantEntity.getMerLogo(),merchantEntity.getCardFrontPhoto(),
                merchantEntity.getCardBackPhoto(),merchantEntity.getBusinessLicensePhoto(),
                merchantEntity.getShopLatitude(),merchantEntity.getShopLongitude(),
                merchantEntity.getIsAuth(),
                merchantEntity.getPerCapitaPrice(),merchantEntity.getShopId()
            });
    }

    @Override
    public MerchantEntity getMerchantId(long id) {
        String sql = "select a.*,"
                + " IFNULL(a.follow_number,0) followNumber,"
                + " IFNULL(a.browse_number,0) browseNumber,"
                + " b.title as title,"
    			+ " b.content as content,"
    			+ " b.image as image,"
    			+ " d.name shopTypeName,"
		        + " e.name shopTypeTwoName"
                + " from t_merchant a "
                + " left join t_business_inform b on b.shop_id = a.shop_id "
                + " left join t_business_category d on d.b_id = a.shop_type "
				+ " left join t_business_category e on e.b_id = a.shop_type_two "
                + " where 1 = 1 and a.shop_id = '"+id+"' ";
        MerchantEntity merchantEntity = new MerchantEntity();
        try {
            merchantEntity = (MerchantEntity) jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(MerchantEntity.class));
        } catch (Exception e) {
        }
        return merchantEntity;
    }

    @Override
    public MerchantEntity getMerchantUserName(String userName) {
        
        String sql = "select shop_id,"
                + " user_name,"
                + " user_pwd,"
                + " is_auth"
                + " from t_merchant where 1 = 1 and user_name = '"+userName+"' ";
        MerchantEntity merchantEntity = new MerchantEntity();
        try {
            merchantEntity = (MerchantEntity) jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(MerchantEntity.class));
        } catch (DataAccessException e) {
        }
        return merchantEntity;
    }

    
	@Override
	public List<MerchantEntity> getAllMerchant(MerchantSearchEntity merchantSearchEntity,int pageNo, int pageSize) {
    	int beginRow = (pageNo - 1)* pageSize;
    	
		String sql = "select a.*,"
				+ " IFNULL(a.follow_number,0) followNumber,"
		        + " IFNULL(a.browse_number,0) browseNumber,"
				+ " b.title,"
				+ " b.content,"
				+ " b.image,"
				+ " c.imgUrl imgUrl,"
				+ " c.name shopTypeName,"
				+ " d.name shopTypeTwoName,"
			    + " (select count(*) from t_coupon where shop_id = a.shop_id and overdue_state = 2 and coupon_type = 2 and shelves_state = 1) overdue_state "
				+ " from t_business_inform b"
				+ " left join t_merchant a on b.shop_id = a.shop_id"
				+ " left join t_business_category c on c.b_id = a.shop_type "
				+ " left join t_business_category d on d.b_id = a.shop_type_two "
				+ " left join t_coupon e on e.shop_id = b.shop_id"
				+ " where 1 = 1 and a.if_del = '0' ";
		
			//一级分类查询
			if (merchantSearchEntity.getbCategoryOneId() != 0) {
	            sql += " and a.shop_type = '"+merchantSearchEntity.getbCategoryOneId()+"' ";
	        }
	        
			//二级分类查询
	        if (merchantSearchEntity.getbCategoryTwoId() != 0) {
	            sql += " and a.shop_type_two = '"+merchantSearchEntity.getbCategoryTwoId()+"' ";
	        }
	        
	        //价格排序
	        if (merchantSearchEntity.getMinMoney() <= 0 && merchantSearchEntity.getMaxMoney() <= 0) {
				
			} else {
				if (merchantSearchEntity.getMaxMoney() <= 0) {
					sql += " and a.avg_price >= "+merchantSearchEntity.getMinMoney();
				} else {
					sql +=" and a.avg_price >= "+merchantSearchEntity.getMinMoney()+" and a.avg_price <= "+merchantSearchEntity.getMaxMoney();
				}
			}
	        
	        //0 立减 1 满减 2 体验券 3 不限
	        if (merchantSearchEntity.getType() != 0) {
	        	sql += " and e.type = " + merchantSearchEntity.getType();
	        	
	        	sql += " and e.shelves_state = 1";
	        }
	        
	        sql += " group by a.shop_id ";
	        
	        //1 智能排序 2 人气最高 3 最新上线 4 价格最高 5价格最低
	        if (merchantSearchEntity.getSortType() == 2) {
	        	sql += " order by a.browse_number DESC ";
	        }else if (merchantSearchEntity.getSortType() == 4) {
	        	sql += " order by a.avg_price DESC ";
	        }else if (merchantSearchEntity.getSortType() == 5) {
	        	sql += " order by a.avg_price ASC ";
	        }else {
	        	sql += " order by a.created_time DESC ";
	        }
			
			sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
			
			return jdbcTemplate.query(sql, new BeanPropertyRowMapper(MerchantEntity.class));
	}

	@Override
	public void updateFollowNumber(long shopId) {
		
		String sql = "update t_merchant set follow_number = follow_number +1 where shop_id = "+shopId+" ";
		
		jdbcTemplate.update(sql);
	}
	
	@Override
	public void updateFollowNumberCancel(long shopId) {
		
		String sql = "update t_merchant set follow_number = follow_number -1 where shop_id = "+shopId+" ";
		
		jdbcTemplate.update(sql);
	}

	@Override
	public void updateBrowseNumber(long shopId) {

		String sql = "update t_merchant set browse_number = browse_number +1 where shop_id = "+shopId+" ";
		
		jdbcTemplate.update(sql);
	}

	@Override
	public int updateIsAuth(long id,int isAuth,String refuse) {

		String sql = "update t_merchant set is_auth = "+isAuth+" , refuse = '"+refuse+"' where shop_id = " + id;
		
		return jdbcTemplate.update(sql);
	}

	@Override
	public int getWebListMerchantCount(int isAuth, String shopName) {
		String sql = "select count(*) from t_merchant a "
				+ " left join t_business_category b on b.b_id = a.shop_type "
				+ " left join t_business_category c on c.b_id = a.shop_type_two "
				+ "where 1 = 1  and a.if_del = '0' ";
        
        if (StringUtil.isNotEmpty(shopName) && !"undefined".equals(shopName) ) {
            sql += " and a.shop_name like '%"+shopName+"%' ";
        }
        
        if (isAuth != 0) {
        	sql += "and a.is_auth = " + isAuth;
        }
        
        return jdbcTemplate.queryForInt(sql);
	}

	@Override
	public List<MerchantEntity> getWebAllMerchant(int isAuth, String shopName, int pageNo, int pageSize) {
		
		int beginRow = (pageNo - 1)* pageSize;
		
		String sql = "select a.*,"
				+ " IFNULL(a.follow_number,0) followNumber,"
		        + " IFNULL(a.browse_number,0) browseNumber,"
		        + " b.name shopTypeName,"
		        + " c.name shopTypeTwoName"
				+ " from t_merchant a "
				+ " left join t_business_category b on b.b_id = a.shop_type "
				+ " left join t_business_category c on c.b_id = a.shop_type_two "
				+ " where 1 = 1 and a.if_del = '0' ";
		
			if (StringUtil.isNotEmpty(shopName) && !"undefined".equals(shopName) ) {
	            sql += " and a.shop_name like '%"+shopName+"%' ";
	        }
	        
	        if (isAuth != 0) {
	        	sql += "and a.is_auth = " + isAuth;
	        }else {
	        	sql +=  "and (a.is_auth = 1 or a.is_auth = 2) ";
	        }
			sql += " order by a.created_time DESC ";
			sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
			
			return jdbcTemplate.query(sql, new BeanPropertyRowMapper(MerchantEntity.class));
	}

	@Override
	public int deleteMerchantId(long shopId) {
		
		String sql = "update t_merchant set if_del = 1 where shop_id = "+shopId+" ";
		
		return jdbcTemplate.update(sql);
	}

	@Override
	public MerchantEntity getMerchantShopId(long shopId) {

		String sql = "select * from t_merchant where shop_id = "+shopId;
		
		return (MerchantEntity) jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(MerchantEntity.class));
	}

	

}
